/*
 * employeeDAO.java
 *
 * Created on March 17, 2007, 2:14 PM
 */

package edu.byu.isys413.mystuff;

import java.util.*;
import java.sql.*;

/**
 *
 * @author Sam Griffiths
 */
public class employeeDAO {
    
    //holds instance of itself to always return same instance
    //creation of global variables
    private static employeeDAO instance;
    private String empid;
    private static Cache cache;
    
    /** Creates a new instance of employeeDAO */
    public employeeDAO() {
    cache = Cache.getInstance();
    }
        
    //returns an instance of this class
    public static synchronized employeeDAO getInstance() {
        if (instance == null) {
            instance = new employeeDAO();
            
        }
        return instance;
    }
    
    //creates a new employee object with new id initialized
    public employeeBO create(){
        empid = Guid.generate();
        employeeBO emp = new employeeBO(empid);
        cache.put(empid, emp);
        return emp;
    }
    
    /*
     *This method reads all employees from database and returns them in an ArrayList
     */
    public synchronized ArrayList<employeeBO> readALL() throws DataException {
        ArrayList<employeeBO> emplist = new ArrayList();
        Connection conn = null;
        try {
            //retrieve connection, create read statement, execute
            conn = ConnectionPool.getInstance().get();
            //System.out.println("Check");
            Statement st = conn.createStatement();
            //System.out.println("Executing Query");
            ResultSet rs = st.executeQuery("SELECT * FROM EMPLOYEE_TABLE");
            //System.out.println("Execute Succesful");
            
            while(rs.next()){
                //initialize all parts of new employeeBO
                employeeBO emp = new employeeBO(rs.getString("EMPLOYEEID"));
                emp.setFirstName(rs.getString("FIRST_NAME"));//artist
                emp.setLastName(rs.getString("LAST_NAME"));//album
                emp.setHireDate(rs.getString("HIRE_DATE"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setSalary(Double.valueOf(rs.getString("SALARY").trim()).doubleValue());
                emp.setStoreId(rs.getString("STOREID"));
                emp.setAlreadyInDatabase(true);
                //adds employee object to ArrayList
                emplist.add(emp);
            }
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return emplist;
    }
    
    //finds connection to database and then hands that and an employee id to a different read method
    public synchronized employeeBO read(String empid) throws DataException{
        employeeBO str = null;
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            if(cache.containsKey(empid))
                str = (employeeBO)cache.get(empid);
            else
                str = read(conn, empid);
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return str;
    }
    
    //exactly like read all, but only reads one object and connection is handed in.
    public synchronized employeeBO read(Connection conn, String id){
       employeeBO emp = null;
        try{
        Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM EMPLOYEE_TABLE WHERE EMPLOYEEID='"+id+"'");
            emp = new employeeBO(id);
            while(rs.next()){
                emp.setFirstName(rs.getString("FIRST_NAME"));//artist
                emp.setLastName(rs.getString("LAST_NAME"));//album
                emp.setHireDate(rs.getString("HIRE_DATE"));
                emp.setPhone(rs.getString("PHONE"));
                emp.setSalary(Double.valueOf(rs.getString("SALARY").trim()).doubleValue());
                emp.setStoreId(rs.getString("STOREID"));
                emp.setAlreadyInDatabase(true);
                            
            }
       }catch(Exception e){}
       cache.put(id, emp);
       return emp;
    }

    
    /////////////save////////////////////save an employee bo
    public synchronized boolean save(employeeBO bo) throws DataException {
       System.out.println("Save entrance accessed");
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
            //checks to see if employee bo has changed since read or saved, 
            //won't save again if the object has not change'
            if(bo.isDirty())
                save(conn,bo);
            bo.setDirty(false);
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
        }
    
    //second save, checks to see if already in database, if yes create and execute update sql statement
    //if no create and execute insert statement
    public synchronized void save(Connection conn, employeeBO bo){
        System.out.println("Second Save Accessed");
        try{
            Statement st = conn.createStatement();
            String empid = "";
           
            System.out.println("First Query Skipped");
            if(false == bo.isAlreadyInDatabase()){
                
                String queryString = "INSERT INTO EMPLOYEE_TABLE VALUES (?,?,?,?,?,?,?)";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
               
                //String aString = Double.toString(aDouble);
                pstmt.setString(1,bo.getEmpId());
                pstmt.setString(2,bo.getFirstName());
                pstmt.setString(3,bo.getLastName());
                pstmt.setString(4,bo.getHireDate());
                pstmt.setString(5,bo.getPhone());
                pstmt.setString(6,Double.toString(bo.getSalary()));
                pstmt.setString(7,bo.getStoreid());
                pstmt.execute();
                pstmt.close();
                bo.setAlreadyInDatabase(true);
            } else{
            
                String queryString = "UPDATE EMPLOYEE_TABLE SET FIRST_NAME = ?, LAST_NAME = ? , HIRE_DATE = ?, PHONE = ?, SALARY = ?, STOREID = ? WHERE EMPLOYEEID = ? ";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                pstmt.setString(1,bo.getFirstName());
                pstmt.setString(2,bo.getLastName());
                pstmt.setString(3,bo.getHireDate());
                pstmt.setString(4,bo.getPhone());
                pstmt.setString(5,Double.toString(bo.getSalary()));
                pstmt.setString(6,bo.getStoreid());
                pstmt.setString(7,bo.getEmpId());
                pstmt.execute();
                pstmt.close();
                
            }
        }catch(Exception e){}
    }
    
    /////////////delete////////////////////////
    /*
        We're assuming we will never need to delete a store, if a faulty one is made we can just set it update it 
     *  to an actual new store. No problems, We shouldn't be deleting stores, anyways.
     */
    
}
